package wang;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class exportXlsxFileForjiji {

    //获取所有表格文件信息
    public static List<RecodObj> getDataForFile(String path) throws IOException {
        String classPath ="";
        File arr = new File(path);
        String [] tarFileName = null;
        if(arr.isDirectory()){
            tarFileName =arr.list();
        }

     //   System.out.println("共有文件数量：" +  tarFileName.length);
     //   Arrays.stream(tarFileName).forEach(obj -> System.out.println(obj));

        List<RecodObj> list = new ArrayList<>();
        for (int i=0 ;i<tarFileName.length;i++) {
            /**
             * 1.xls
             * 2.xls
             * 3.xls
             * ~$总表.xlsx  : 跳过临时文件
             */
            if(tarFileName[i].startsWith("~$")){continue;}
            File excel = new File(path +tarFileName[i]);
            Workbook workbook =null;
            FileInputStream fs = null;
            try {
                fs = new FileInputStream(excel);
//                MemoryStream ms = new MemoryStream();
//                //将文件的流拷贝一份，以内存流的格式保存
//                CopyStream(FileUpload1.PostedFile.InputStream, m);
                workbook = WorkbookFactory.create(fs);
            } catch (Exception e) {
              e.printStackTrace();
            }
            Sheet sheet_source = null;
           sheet_source = workbook.getSheetAt(0);
            Cell cell = null;
            int rowNum=0;
            for (Row r:sheet_source) {
                // 略过标题行
                if (r.getRowNum() < 1) {
                    continue;
                }
                rowNum ++;
                int colIdx = 0;
                RecodObj  recodObj = new RecodObj();
                  //受理序号
                 String  order = null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    order = cell.getStringCellValue();
                }

                //工单号
                 String  workNum= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    workNum = cell.getStringCellValue();
                }
                //专业
                 String  speciality= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    speciality = cell.getStringCellValue();
                }
                //系统
                 String  systemName= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    systemName = cell.getStringCellValue();
                }
                //工单日期
                 String  dateStr= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    dateStr = cell.getStringCellValue();
                }
                //工单时间
                 String  timeStr= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    timeStr = cell.getStringCellValue();
                }
                //故障人
                 String  problemPerson= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    problemPerson = cell.getStringCellValue();
                }
                //联系电话
                 String  telephone= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    telephone = cell.getStringCellValue();
                }
                //故障人单位
                 String  uniteNmae= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    uniteNmae = cell.getStringCellValue();
                }
                //受理人
                 String  acceptancePerson= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    acceptancePerson = cell.getStringCellValue();
                }
                //处理人
                 String  handlePerson= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    handlePerson = cell.getStringCellValue();
                }
                // 报障渠道
                 String  route= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    route = cell.getStringCellValue();
                }
                //报障类型
                 String  routeType= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    routeType = cell.getStringCellValue();
                }
                //故障详细内容
                 String  routeConten= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    routeConten = cell.getStringCellValue();
                }
                //问题分析（是否有解决方案）
                 String  problemAnalysis= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    problemAnalysis = cell.getStringCellValue();
                }
                //场景分析标签
                 String  sceneAnalysis= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    sceneAnalysis = cell.getStringCellValue();
                }
                //现有解决方案是否有效
                 String  analysisEffectiveflag= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    analysisEffectiveflag = cell.getStringCellValue();
                }
                //处理结果
                 String  handleResults= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    handleResults = cell.getStringCellValue();
                }
                //故障次数
                 Integer  problemNum= null;
                cell = r.getCell(colIdx++);
                if(cell.getNumericCellValue() >0){
                    problemNum =new Double( cell.getNumericCellValue()).intValue();
                }
                //是否转二线
                 String  turnTwoFlag= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    turnTwoFlag = cell.getStringCellValue();
                }
                //ITSM单号(省侧)
                 String  itsm= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    itsm = cell.getStringCellValue();
                }
                //实际完成时间(分)
                 String timeConsuming= null;
                cell = r.getCell(colIdx++);
                if(cell.getNumericCellValue() >0){
                    Integer min =new Double( cell.getNumericCellValue()).intValue();
                    timeConsuming = min.toString();
                }
                //模块
                 String  module= null;
                cell = r.getCell(colIdx++);
                if(cell.getStringCellValue() !=null){
                    module = cell.getStringCellValue();
                }


                /**
                 * 保存
                 */
                recodObj.setOrder(order);
                recodObj.setWorkNum( workNum);
                recodObj.setSpeciality( speciality);
                recodObj.setSystemName( systemName);
                recodObj.setDateStr( dateStr);
                recodObj.setTimeStr(timeStr);
                recodObj.setProblemPerson( problemPerson);
                recodObj.setTelephone(telephone);
                recodObj.setUniteNmae( uniteNmae);
                recodObj.setAcceptancePerson(acceptancePerson);
                recodObj.setHandlePerson( handlePerson);
                recodObj.setRoute(route);
                recodObj.setRouteType( routeType);
                recodObj.setRouteConten( routeConten);
                recodObj.setProblemAnalysis(problemAnalysis);
                recodObj.setSceneAnalysis( sceneAnalysis);
                recodObj.setAnalysisEffectiveflag(analysisEffectiveflag);
                recodObj.setHandleResults( handleResults);
                recodObj.setProblemNum(problemNum);
                recodObj.setTurnTwoFlag( turnTwoFlag);
                recodObj.setItsm(itsm);
                recodObj.setTimeConsuming( timeConsuming);
                recodObj.setModule( module);
                if(recodObj.getOrder()==null && recodObj.getWorkNum()==null){
                   //当前行无效或已经读完
                    continue;
                }
                list.add(recodObj);
            }

            if (fs != null) {
                fs.close();
            }
            if (workbook != null) {
                workbook.close();
            }
            System.out.println(excel.getName() +",读出了："+rowNum +"条数据！准备读取下一个表格文件!");
        }
        return list;
    }

    //读取模板填充
    public static void addDataTotemplate(File tarFile, Workbook workbook, List<RecodObj> list) throws Exception {
        if(tarFile.exists()==false){tarFile.createNewFile();}
        Sheet sheet_0=  workbook.getSheetAt(0);
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet_0.createRow(i + 1);
            RecodObj obj = list.get(i);
            row.createCell(0).setCellValue(obj.getOrder());

            row.createCell(1).setCellValue(obj.getWorkNum());

            row.createCell(2).setCellValue(obj.getSpeciality());

            row.createCell(3).setCellValue(obj.getSystemName());

            row.createCell(4).setCellValue(obj.getDateStr());

            row.createCell(5).setCellValue(obj.getTimeStr());

            row.createCell(6).setCellValue(obj.getProblemPerson());

            row.createCell(7).setCellValue(obj.getTelephone());

            row.createCell(8).setCellValue(obj.getUniteNmae());

            row.createCell(9).setCellValue(obj.getAcceptancePerson());

            row.createCell(10).setCellValue(obj.getHandlePerson());

            row.createCell(11).setCellValue(obj.getRoute());

            row.createCell(12).setCellValue(obj.getRouteType());

            row.createCell(13).setCellValue(obj.getRouteConten());

            row.createCell(14).setCellValue(obj.getProblemAnalysis());

            row.createCell(15).setCellValue(obj.getSceneAnalysis());

            row.createCell(16).setCellValue(obj.getAnalysisEffectiveflag());

            row.createCell(17).setCellValue(obj.getHandleResults());

            row.createCell(18).setCellValue(String.valueOf(obj.getProblemNum()));

            row.createCell(19).setCellValue(obj.getTurnTwoFlag());

            row.createCell(20).setCellValue(obj.getItsm());

            row.createCell(21).setCellValue(String.valueOf(obj.getTimeConsuming()));

            row.createCell(22).setCellValue(obj.getModule());
            
        }
        OutputStream outputStream = new FileOutputStream(tarFile);
        workbook.write(outputStream);
        System.out.println("工单合并完成，路径：" +tarFile.getAbsolutePath());
    }

    //创建导出模板
    public static Workbook copyTemplate () throws Exception {
        String path = exportXlsxFileForjiji.class.getClassLoader().getResource("Template.xlsx").getPath();
         File Copyfile = new File(path);
        Workbook  workbook =null;
        if(Copyfile !=null){
            workbook =  new XSSFWorkbook(Copyfile);
        }else{
            System.out.println("读取模板文件失败！");
        }
        return workbook ;
    }

    //方式一：读取单个字节，输出单个字节
    public static void fileByte(File source, File target) throws IOException {
        long start = System.currentTimeMillis();
        FileInputStream fis = new FileInputStream(source);
        FileOutputStream fos = new FileOutputStream(target);

        int data;
        while((data = fis.read())!= -1) {
            fos.write(data);
        }
        fis.close();
        fos.close();
        long end = System.currentTimeMillis();
        System.out.println("单字节读取及输出，复制一个Java文件耗时 "+(end - start)+" 毫秒");
    }

    //方式二：用数组存储读取到的字节数据，然后写入
    public static void fileBytes(File source, File target) throws IOException {
        long start = System.currentTimeMillis();
        FileInputStream fis = new FileInputStream(source);
        FileOutputStream fos = new FileOutputStream(target);

        byte[] data = new byte[1024];
        int len;
        while((len = fis.read(data))!=-1) {
            fos.write(data, 0, len);
        }

        fis.close();
        fos.close();
        long end = System.currentTimeMillis();
        System.out.println("多字节读取及输出，复制一个Java文件耗时"+(end - start)+"毫秒");
    }


    //方式三：使用缓冲输入流按字节读取及缓冲输出流来复制
    public static void bufferedByte(File source, File target) throws IOException {
        long start = System.currentTimeMillis();
        FileInputStream fis = new FileInputStream(source);
        FileOutputStream fos = new FileOutputStream(target);
        BufferedInputStream bis = new BufferedInputStream(fis);
        BufferedOutputStream bos = new BufferedOutputStream(fos);

        int data;
        while((data = bis.read())!=-1) {
            bos.write(data);
        }
        bis.close();
        bos.close();
        long end = System.currentTimeMillis();
        System.out.println("使用缓冲输入流读取单字节，缓冲输出流来复制，耗时"+(end - start)+"毫秒");
    }

    //方式四：使用缓冲输入流及缓冲输出流来复制
    public static void bufferedBytes(File source, File target) throws IOException {
        long start = System.currentTimeMillis();
        FileInputStream fis = new FileInputStream(source);
        FileOutputStream fos = new FileOutputStream(target);
        BufferedInputStream bis = new BufferedInputStream(fis);
        BufferedOutputStream bos = new BufferedOutputStream(fos);

        byte[] data = new byte[1024];
        int len;
        while((len = bis.read(data))!=-1) {
            bos.write(data,0,len);
        }
        bis.close();
        bos.close();
        long end = System.currentTimeMillis();
        System.out.println("使用缓冲输入流读取单字节，缓冲输出流来复制，耗时"+(end - start)+"毫秒");
    }

}
